import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Database {
    private Connection connection;
    private Statement statement;

    public Database() {
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3305/mydatabase?useSSL=false", "root", "12345678");
            statement = connection.createStatement();
            createTables();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void createTables() {
        try {
            String createCoursesTable = "CREATE TABLE IF NOT EXISTS `courses` ("
                    + "id INT AUTO_INCREMENT PRIMARY KEY,"
                    + "name VARCHAR(255) NOT NULL,"
                    + "teacher VARCHAR(255) NOT NULL,"
                    + "time VARCHAR(255) NOT NULL,"
                    + "location VARCHAR(255) NOT NULL)";
            statement.executeUpdate(createCoursesTable);

            String createStudentsTable = "CREATE TABLE IF NOT EXISTS students ("
                    + "id INT AUTO_INCREMENT PRIMARY KEY,"
                    + "student_id VARCHAR(255) NOT NULL,"
                    + "name VARCHAR(255) NOT NULL,"
                    + "contact VARCHAR(255) NOT NULL)";
            statement.executeUpdate(createStudentsTable);

            String createRegistrationsTable = "CREATE TABLE IF NOT EXISTS registrations ("
                    + "id INT AUTO_INCREMENT PRIMARY KEY,"
                    + "student_id VARCHAR(255) NOT NULL,"
                    + "course_name VARCHAR(255) NOT NULL)";
            statement.executeUpdate(createRegistrationsTable);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void addCourse(Course course) {
        try {
            String query = "INSERT INTO courses (name, teacher, time, location) VALUES (?,?,?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(query);
            preparedStatement.setString(1, course.name);
            preparedStatement.setString(2, course.teacher);
            preparedStatement.setString(3, course.time);
            preparedStatement.setString(4, course.location);
            preparedStatement.executeUpdate();
            System.out.println("成功添加课程: " + course.name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void addStudent(Student student) {
        try {
            String query = "INSERT INTO students (student_id, name, contact) VALUES (?,?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(query);
            preparedStatement.setString(1, student.studentId);
            preparedStatement.setString(2, student.name);
            preparedStatement.setString(3, student.contact);
            preparedStatement.executeUpdate();
            System.out.println("成功添加学生: " + student.name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public boolean studentRegister(String studentId, String courseName) {
        try {
            Student student = getStudent(studentId);
            Course course = getCourse(courseName);
            if (student!= null && course!= null) {
                String query = "INSERT INTO registrations (student_id, course_name) VALUES (?,?)";
                PreparedStatement preparedStatement = connection.prepareStatement(query);
                preparedStatement.setString(1, studentId);
                preparedStatement.setString(2, courseName);
                preparedStatement.executeUpdate();
                System.out.println(student.name + " 成功注册课程: " + courseName);
                return true;
            }
            System.out.println("注册失败，学生或课程不存在。");
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public Student getStudent(String studentId) {
        try {
            String query = "SELECT * FROM students WHERE student_id =?";
            PreparedStatement preparedStatement = connection.prepareStatement(query);
            preparedStatement.setString(1, studentId);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return new Student(resultSet.getString("student_id"), resultSet.getString("name"),
                        resultSet.getString("contact"));
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public Course getCourse(String courseName) {
        try {
            String query = "SELECT * FROM courses WHERE name =?";
            PreparedStatement preparedStatement = connection.prepareStatement(query);
            preparedStatement.setString(1, courseName);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return new Course(resultSet.getString("name"), resultSet.getString("teacher"),
                        resultSet.getString("time"), resultSet.getString("location"));
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public Map<String, List<String>> generateClassLists() {
        Map<String, List<String>> classLists = new HashMap<>();
        try {
            String query = "SELECT s.name, c.name FROM registrations r "
                    + "JOIN students s ON r.student_id = s.student_id "
                    + "JOIN courses c ON r.course_name = c.name";
            ResultSet resultSet = statement.executeQuery(query);
            while (resultSet.next()) {
                String studentName = resultSet.getString("s.name");
                String courseName = resultSet.getString("c.name");
                classLists.computeIfAbsent(courseName, k -> new ArrayList<>()).add(studentName);
            }
            return classLists;
        } catch (SQLException e) {
            e.printStackTrace();
            return classLists;
        }
    }

    public void close() {
        try {
            if (statement!= null) {
                statement.close();
            }
            if (connection!= null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
